package com.yuanfeng.action.front.index.dao;

import com.yuanfeng.pojo.Books;
import com.yuanfeng.pojo.Types;
import com.yuanfeng.util.JdbcUtil;
import com.yuanfeng.util.WebUtil;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

/**
 * 首页dao实现
 *
 * @author yuanfeng
 */
public class IndexDaoImpl extends HibernateDaoSupport implements IndexDao {

    /**
     * 查询所有图书分类
     * @return
     */
    public List<Types> queryTypes() {

        return this.getHibernateTemplate().find("from Types");
    }

    /**
     * 根据分类id查询图书
     * @param typeId
     * @return
     */
    public List<Books> queryBooks(final String typeId) {

        return this.getHibernateTemplate().executeFind(new HibernateCallback<List<Books>>() {

            @Override
            public List<Books> doInHibernate(Session session) throws HibernateException, SQLException {
                if (!WebUtil.isEmpty(typeId)) {
                    String hql1 = "select id,name,price,auth,img,round(price*rebate,2) as currentPrice from Books where"
                            + " id in (select bt.bookId from BookType bt where bt.typeId = ?)";
                    Query query = session.createQuery(hql1);
                    query.setParameter(0, typeId);
                    return transferCode(query);
                } else {
                    String hql2 = "select id,name,price,auth,img,round(price*rebate,2) as currentPrice from Books";
                    Query query = session.createQuery(hql2);
                    return transferCode(query);
                }
            }
        });
    }

    /**
     * 封装底层的object[]数组到List<Books>中
     * @param query
     * @return
     */
    public List<Books> transferCode(Query query) {
        List bookData = query.list();
        List bookList = new ArrayList();
        for (int i = 0; i < bookData.size(); i++) {
            Books book = new Books();
            Object[] object = (Object[]) bookData.get(i);
            book.setId(object[0].toString());
            book.setName(object[1].toString());
            book.setPrice(Double.parseDouble(object[2].toString()));
            book.setAuth(object[3].toString());
            book.setImg(object[4].toString());
            book.setCurrentPrice(Double.parseDouble(object[5].toString()));
            bookList.add(book);
        }
        System.out.println(bookList);
        return bookList;

    }

    /**
     * 根据分类id查询图书(分页显示)
     * 记得配置spring 中的sessionFactory，不然getHibernateTemplte()会出现空的情况
     * @param typeId
     * @param curPage
     * @param pageSize
     * @return
     */
    public List<Books> queryBooks(final String typeId, final int curPage, final int pageSize) {

        return this.getHibernateTemplate().executeFind(new HibernateCallback<List<Books>>() {

            @Override
            public List<Books> doInHibernate(Session session) throws HibernateException, SQLException {
                if (!WebUtil.isEmpty(typeId)) {
                    String hql1 = "select id,name,price,auth,img,round(price*rebate,2) as currentPrice from Books where"
                            + " id in (select bt.bookId from BookType bt where bt.typeId = ?)";
                    Query query = session.createQuery(hql1);
                    query.setParameter(0, typeId);
                    query.setFirstResult((curPage - 1) * pageSize);
                    query.setMaxResults(pageSize);
                    return transferCode(query);
                } else {
                    String hql2 = "select id,name,price,auth,img,round(price*rebate,2) as currentPrice from Books";
                    Query query = session.createQuery(hql2);
                    query.setFirstResult((curPage - 1) * pageSize);
                    query.setMaxResults(pageSize);
                    return transferCode(query);
                }
            }
        });
    }

    /**
     * 分类后总计数量
     * @param typeId
     * @return
     */
    public int queryBooksCount(String typeId) {

        Long count = 0L;
        if (!WebUtil.isEmpty(typeId)) {
            String hql = "select count(*) from Books where 1=1 " +
                    "and id in(select bookId from BookType where typeId='" + typeId + "')";

            Query query = this.getSessionFactory().getCurrentSession().createQuery(hql);
            count = (Long) query.uniqueResult();

        }
        return count.intValue();
    }

    /**
     * 根据id查询图书
     * @param bookId
     * @return
     */
    public Books queryBook(String bookId) {
        try {
            String sql = "select id,name,price,rebate,auth,"
                    + "img,round(price*rebate,2) as currentPrice,"
                    + "publisher,publishdate,pages,size,"
                    + "printtimes,versions,brief,content,onlinetime from books where id=?";
            QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
            Books book = (Books)qr.query(sql, new BeanHandler(Books.class), new Object[]{bookId});
            return book;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }

    }
}
